package cn.edu.nju.software.sqlitemanage;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import cn.edu.nju.software.beans.StudentBean;

public class ManageXueSheng {
	// add students to own base
	public static boolean addStudents(ArrayList<StudentBean> newStudents)
			throws SQLException, ClassNotFoundException {

		Connection conn = ManageConnection.getConnection();
		boolean updateOk = false;

		try {
			String command = "INSERT INTO xuesheng VALUES (?,?,?,?)";
			PreparedStatement prepareState = conn.prepareStatement(command);

			for (StudentBean student : newStudents) {
				String sid = student.getId();
				String sname = student.getName();
				String sgender = student.getGender();
				String sinstitute = student.getInstitute();

				prepareState.setString(3, sid);
				prepareState.setString(1, sname);
				prepareState.setString(2, sgender);
				prepareState.setString(4, sinstitute);

				prepareState.executeUpdate();
			}
			updateOk = true;
		} catch (Exception exc) {
			updateOk = false;
		} finally {
			conn.close();
		}

		return updateOk;
	}

	// delete a student by given id
	public static boolean deleteStudent(ArrayList<String> sids)
			throws SQLException, ClassNotFoundException {

		Connection conn = ManageConnection.getConnection();
		boolean updateOk = false;

		try {
			String command = "DELETE FROM xuesheng WHERE sid = ?";
			PreparedStatement prepareState = conn.prepareStatement(command);

			for (String sid : sids) {
				prepareState.setString(1, sid);

				prepareState.executeUpdate();
			}
			updateOk = true;
		} catch (Exception exc) {
			updateOk = false;
		} finally {
			conn.close();
		}

		return updateOk;
	}

	// get all students of own institute
	public static ArrayList<StudentBean> getAllStudents() throws SQLException,
			ClassNotFoundException {

		Connection conn = ManageConnection.getConnection();
		ArrayList<StudentBean> allStudents = new ArrayList<StudentBean>();
		String institute = "B";

		try {
			String command = "SELECT * FROM xuesheng WHERE whichdepartment = ?";
			PreparedStatement prepareState = conn.prepareStatement(command);
			prepareState.setString(1, institute);

			ResultSet rs = prepareState.executeQuery();
			while (rs.next()) {
				String sid = rs.getString(3);
				String sname = rs.getString(1);
				String sgender = rs.getString(2);
				String sinstitute = rs.getString(4);

				StudentBean getOne = new StudentBean(sid, sname, sgender,
						sinstitute);
				allStudents.add(getOne);
			}
		} catch (Exception exc) {

		} finally {
			conn.close();
		}

		return allStudents;
	}

	// get students by given id list
	public static ArrayList<StudentBean> getStudents(ArrayList<String> sids)
			throws SQLException, ClassNotFoundException {

		Connection conn = ManageConnection.getConnection();
		ArrayList<StudentBean> targetStudents = new ArrayList<StudentBean>();

		try {
			String command = "SELECT * FROM xuesheng WHERE sid = ?";
			PreparedStatement prepareState = conn.prepareStatement(command);

			for (String sid : sids) {
				prepareState.setString(1, sid);
				ResultSet rs = prepareState.executeQuery();
				while (rs.next()) {
					String sname = rs.getString(1);
					String sgender = rs.getString(2);
					String sinstitute = rs.getString(4);

					StudentBean getOne = new StudentBean(sid, sname, sgender,
							sinstitute);
					targetStudents.add(getOne);
				}
			}
		} catch (Exception exc) {

		} finally {
			conn.close();
		}

		return targetStudents;
	}
}
